-- **************************************************************************
--    Project Name:   接件准点率-汇总/明细数据
--    Job Name:       jms_dm__dm_tab_day_recevie_total_dt
--    Description :   根据运单末端中心与末端支线站点的规划/实际接件时间计算是否接件准点
--    Author :        荣光凡
--    date：          2022/04/01
-- **************************************************************************
--    Relation ：
--    jms_dm.dm_detail << [
--       jms_dwd.dwd_tab_barscan_loading_base_dt,
--       jms_dim.dim_lmdm_sys_network,
--       jms_ods.registration_problem_piece,
--       jms_ods.tab_end_piece,
--       jms_dwd.dwd_tab_barscan_arrival_base_dt,
--       jms_ods.yl_tms_shipment,
--       jms_ods.yl_tms_branch_time_effective
--    ]
--    jms_dm.dm_tatol << [
--       jms_dm.dm_detail
--    ]
-- **************************************************************************
--    modify by 荣光凡 2022/04/01 新增脚本
-- **************************************************************************

-- 基础数据获取-装车表中心装车下一站集散点/网点
--insert overwrite table jms_tmp.tmp_v1
with tab as (
select
    -- 装车数据数据 A装B卸
    loading.billcode
    ,loading.scansitecode           pre_network_code
    ,loading.nextstationcode        recevie_network_code
    ,loading.inputsite              pre_network_name
    ,loading.nextstation            recevie_network_name
    ,loading.transfercode           shipment_no
    -- 纬度信息
    ,arr_net.financial_center_desc agent_name
    ,arr_net.financial_center_code agent_code
    ,arr_net.parent_network_name   fran_name
    ,arr_net.parent_network_code   fran_code
    -- 问题件标记
    ,if(problem.filter_type is not null,problem.filter_type,0) as filter_type
    ,loading.scantime                                             -- 装车扫描时间
    -- 装车站点到件时间
    ,arr_t.pre_arr_date                                           -- 始发站点到件时间
   --  --  实际发车时间没有匹配到，就用装车发件的时间作为实际发车时间
    ,shipno.actual_departure_time                                 -- 实际离开时间
from (
    select
        billcode
        ,scantime
        ,scansitecode
        ,nextstationcode
        ,inputsite
        ,nextstation
        ,transfercode
    from (    --  接件准点率-明细基础数据
        select
            waybill_no billcode
            ,scan_time scantime
            ,scan_site_code scansitecode
            ,next_station_code nextstationcode
            ,scan_site inputsite
            ,next_station nextstation
            ,shipment_no transfercode
            ,row_number() OVER(PARTITION BY waybill_no, scan_site_code, next_station_code ORDER BY scan_time) rn
        from jms_dwd.dwd_tab_barscan_loading_base_dt
        where dt = date_add('{{ execution_date | cst_ds }}',0) --and date_add('{{ execution_date | cst_ds }}', 1)
        and substr(scan_time,1,10) = date_add('{{ execution_date | cst_ds }}',0) --and date_add('{{ execution_date | cst_ds }}', 1)
        and (waybill_no like 'JT%' or waybill_no like '55%')
    ) tmp
    where rn = 1
) loading
inner join (
    select code
    from jms_dim.dim_lmdm_sys_network
    where is_distribution_center = 1 or is_entrepot = 1
) send_net
on loading.scansitecode = send_net.code
inner join (
    select code
        ,financial_center_desc
        ,financial_center_code
        ,parent_network_name
        ,parent_network_code
    from jms_dim.dim_lmdm_sys_network
    where type_id = 336 or is_entrepot = 1
) arr_net
on loading.nextstationcode = arr_net.code
left join (
    -- part_02
    -- 接件准点率-拦截件\退回件\完结件标记
    select waybill_no as billcode,min(filter_type) as filter_type
    from (
        select waybill_no,1 as filter_type
        from jms_ods.registration_problem_piece
        where dt between date_add('{{ execution_date | cst_ds }}',0) and date_add('{{ execution_date | cst_ds }}', 1)
        and (second_level_type_code = '28a' or second_level_type_code = '17a')
        and substr(scan_time,1,10) between date_add('{{ execution_date | cst_ds }}', -1) and date_add('{{ execution_date | cst_ds }}',0)
        group by waybill_no

        union all

        select waybill_no,2 as filter_type
        from jms_ods.tab_end_piece
        where dt = date_add('{{ execution_date | cst_ds }}',0)
        and substr(input_time,1,10) between date_add('{{ execution_date | cst_ds }}', -6) and date_add('{{ execution_date | cst_ds }}', 1)
        and (end_code in (5,30,41) or (end_code = 44 and is_delete = 1))
        group by waybill_no
    ) tmp
    group by
        waybill_no
) problem
on loading.billcode = problem.billcode
left join (
    -- 接件准点率-到件数据更新
    select
        waybill_no billcode
        ,scan_site_code scansitecode
        ,min(scan_time) as pre_arr_date
    from jms_dwd.dwd_tab_barscan_arrival_base_dt arrive
    where dt between date_add('{{ execution_date | cst_ds }}', -2) and date_add('{{ execution_date | cst_ds }}',0)
 --   and scantime between date_add('{{ execution_date | cst_ds }}', -1) and date_add('{{ execution_date | cst_ds }}',0)
    group by
        waybill_no
        ,scan_site_code
) arr_t
on loading.billcode = arr_t.billcode
and loading.scansitecode = arr_t.scansitecode
left join (
    --  接件准点率-实际接件时间
    select
        shipment_no
        ,start_code
        ,end_code
        ,max(actual_departure_time) as actual_departure_time
    from jms_dwd.dwd_tmsnew_shipment_union_base_dt
    where dt >= date_add('{{ execution_date | cst_ds }}',-30)
    and dt <= '{{ execution_date | cst_ds }}'
    -- and planned_departure_time >= '2022-01-10 00:00:00'
    -- and planned_departure_time < '2022-01-13 00:00:00'
    group by
        shipment_no
        ,start_code
        ,end_code
) shipno
on loading.transfercode = shipno.shipment_no
and loading.scansitecode = shipno.start_code
and loading.nextstationcode = shipno.end_code
)

insert overwrite table jms_dm.dm_tab_day_recevie_detail_dt partition(dt)
select
    billcode
    ,shipment_no
    ,pre_network_code
    ,pre_network_name
    ,recevie_network_code
    ,recevie_network_name
    ,agent_name as recevie_agent_name
    ,agent_code as recevie_agent_code
    ,fran_name  as recevie_fran_name
    ,fran_code  as recevie_fran_code
    ,filter_type
    ,real_recevie_date      -- 实际接件时间
    ,plan_recevie_date      -- 规划接件时间
    ,if(real_recevie_date <= plan_recevie_date,1,0) as is_in_time -- 1 准点 0 不准点
    ,pre_arr_date
    ,scantime as scan_time
    -- ,actual_departure_time
    -- ,planned_departure_time
    -- ,latest_arrival_time
    ,substr(real_recevie_date,1,10) as dt
from (
    select
        t1.billcode
        ,t1.pre_network_code
        ,t1.recevie_network_code
        ,t1.pre_network_name
        ,t1.recevie_network_name
        ,t1.shipment_no
        ,t1.agent_name
        ,t1.agent_code
        ,t1.fran_name
        ,t1.fran_code
        ,t1.filter_type
        ,t1.scantime
        ,t1.actual_departure_time
        ,t1.real_recevie_date     -- 实际接件时间
        ,t1.pre_arr_date
        ,t2.planned_departure_time
        ,t2.latest_arrival_time
        ,case
            when t2.latest_arrival_time > t2.planned_departure_time
            and unix_timestamp(concat(ymd,t2.latest_arrival_time,':00')) < unix_timestamp(t1.pre_arr_date)
              then from_unixtime(unix_timestamp(concat(ymd,t2.planned_departure_time,':00')) + 86400 * 2,'yyyy-MM-dd HH:mm:ss')
            when unix_timestamp(concat(ymd,t2.latest_arrival_time,':00')) < unix_timestamp(t1.pre_arr_date)
            or t2.latest_arrival_time > t2.planned_departure_time
              then from_unixtime(unix_timestamp(concat(ymd,t2.planned_departure_time,':00')) + 86400 * 1,'yyyy-MM-dd HH:mm:ss')
            else from_unixtime(unix_timestamp(concat(ymd,t2.planned_departure_time,':00')),'yyyy-MM-dd HH:mm:ss')
        end as plan_recevie_date  -- 计划接件时间
        ,row_number() over(partition by t1.billcode, t1.pre_network_code, t1.recevie_network_code order by (
            CASE
                WHEN unix_timestamp(concat(ymd,t2.latest_arrival_time,':00')) < unix_timestamp(t1.pre_arr_date)
                    THEN unix_timestamp(concat(ymd,t2.latest_arrival_time,':00')) + 86400 * 1
                ELSE unix_timestamp(concat(ymd,t2.latest_arrival_time,':00'))
            END - unix_timestamp(t1.pre_arr_date)
            )
        ) as rn
    from (
        select
            billcode
            ,pre_network_code
            ,recevie_network_code
            ,scantime
            ,pre_network_name
            ,recevie_network_name
            ,shipment_no
            ,agent_name
            ,agent_code
            ,fran_name
            ,fran_code
            ,filter_type
            ,pre_arr_date
            ,actual_departure_time
            ,nvl(actual_departure_time,scantime) as real_recevie_date
            ,substr(pre_arr_date,1,11) as ymd
        from tab
        where pre_arr_date is not null  -- 到件时间不为空
       -- and scantime between date_add('{{ execution_date | cst_ds }}',0) and date_add('{{ execution_date | cst_ds }}',0)
    ) t1
    left join (
        select
            depart_network_code,
            arrive_network_code,
            planned_departure_time,
            latest_arrival_time
        from jms_ods.yl_tms_branch_time_effective
        where dt = date_add('{{ execution_date | cst_ds }}',0)
        and line_type = 2
        and is_enable = 1
        and planned_departure_time <> '24:00'
        and effective_date <= date_add('{{ execution_date | cst_ds }}',0)
        and expiration_date >= date_add('{{ execution_date | cst_ds }}',0)
        group by
            depart_network_code,
            arrive_network_code,
            planned_departure_time,
            latest_arrival_time
    ) t2
    on t1.pre_network_code = t2.depart_network_code
    and t1.recevie_network_code = t2.arrive_network_code

    union all

    select
        t1.billcode
        ,t1.pre_network_code
        ,t1.recevie_network_code
        ,t1.pre_network_name
        ,t1.recevie_network_name
        ,t1.shipment_no
        ,t1.agent_name
        ,t1.agent_code
        ,t1.fran_name
        ,t1.fran_code
        ,t1.filter_type
        ,t1.scantime
        ,t1.actual_departure_time
        ,t1.real_recevie_date     -- 实际接件时间
        ,t1.pre_arr_date
        ,t2.planned_departure_time
        ,t2.latest_arrival_time
        ,CASE
          WHEN unix_timestamp(concat(ymd,t2.planned_departure_time,':00')) < unix_timestamp(t1.scantime)
              THEN from_unixtime(unix_timestamp(concat(ymd,t2.planned_departure_time,':00')) + 86400 * 1,'yyyy-MM-dd HH:mm:ss')
          ELSE from_unixtime(unix_timestamp(concat(ymd,t2.planned_departure_time,':00')),'yyyy-MM-dd HH:mm:ss')
        END plan_recevie_date
        ,row_number() OVER(PARTITION BY t1.billcode, t1.pre_network_code, t1.recevie_network_code ORDER BY (
            CASE
               WHEN unix_timestamp(concat(ymd,t2.planned_departure_time,':00')) < unix_timestamp(t1.scantime)
                  THEN unix_timestamp(concat(ymd,t2.planned_departure_time,':00')) + 86400 * 1
               ELSE unix_timestamp(concat(ymd,t2.planned_departure_time,':00'))
            END - unix_timestamp(t1.scantime)
        )) rn
    from (
        select
            billcode
            ,pre_network_code
            ,recevie_network_code
            ,scantime
            ,pre_network_name
            ,recevie_network_name
            ,shipment_no
            ,agent_name
            ,agent_code
            ,fran_name
            ,fran_code
            ,filter_type
            ,pre_arr_date
            ,actual_departure_time
            ,nvl(actual_departure_time,scantime) as real_recevie_date
            ,substr(scantime,1,11) as ymd
        from tab
        where pre_arr_date is null  -- 到件时间为空
       -- and scantime between date_add('{{ execution_date | cst_ds }}', -2) and date_add('{{ execution_date | cst_ds }}',0)
    ) t1
    left join (
        select
            depart_network_code,
            arrive_network_code,
            planned_departure_time,
            latest_arrival_time
        from jms_ods.yl_tms_branch_time_effective
        where dt = date_add('{{ execution_date | cst_ds }}',0)
        and line_type = 2
        and is_enable = 1
        and planned_departure_time <> '24:00'
        and effective_date <= date_add('{{ execution_date | cst_ds }}',0)
        and expiration_date >= date_add('{{ execution_date | cst_ds }}',0)
        group by
            depart_network_code,
            arrive_network_code,
            planned_departure_time,
            latest_arrival_time
    ) t2
    on t1.pre_network_code = t2.depart_network_code
    and t1.recevie_network_code = t2.arrive_network_code
) tb
where rn = 1 and substr(real_recevie_date,1,10) = '{{ execution_date | cst_ds }}';

insert overwrite table jms_dm.dm_tab_day_recevie_total_dt partition(dt)
select
    pre_network_code
    ,pre_network_name
    ,recevie_network_code
    ,recevie_network_name
    ,recevie_agent_name
    ,recevie_agent_code
    ,recevie_fran_name
    ,recevie_fran_code
    ,count(billcode) as total_num
    ,sum(if(is_in_time=1,1,0)) as intime_total
    ,sum(if(is_in_time=0,1,0)) as not_intime_total
    ,sum(if(is_in_time=1,1,0))/count(billcode) as rate
    ,substr(scan_time,1,10) as load_scan_date
    ,dt as dt
from jms_dm.dm_tab_day_recevie_detail_dt
where filter_type = 0
and dt = date_add('{{ execution_date | cst_ds }}',0)
group by
    pre_network_code
    ,pre_network_name
    ,recevie_network_code
    ,recevie_network_name
    ,recevie_agent_name
    ,recevie_agent_code
    ,recevie_fran_name
    ,recevie_fran_code
    ,substr(scan_time,1,10)
    ,dt
;
